﻿CREATE PROCEDURE [dbo].[proc_Orders_Company]
	(
		@StartDate nvarchar(50),
		@EndDate nvarchar (50),
		@CompanyId int,
		@MainProjectId int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
begin
	
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Orders.Id DESC)AS Row,

	Id,
	OId,
	OrderId,
	username,
	(select d.DName from Department d Where d.id=(select e.departmentid from employee e where e.username=Orders.username)) as DepartmentName,
	(select e.ename from employee e where e.username=Orders.username) as ename,
	CustomerId,
	isnull((Select CustomerName From Customer Where CustomerId=Orders.CustomerId),'...') As CustomerName,
	FixtureDate,
	Aggregateamount,
	Proceeds,
	FinanceFlag,
	MainProjectId,
	(select product.title from product where product.id=MainProjectId) AS MainProjectName
	From Orders 
	Where  
		 CompanyId=@CompanyId
         and (Convert(varchar(10),FixtureDate,120)>=@StartDate and Convert(varchar(10),FixtureDate,120)<=@EndDate)
         and (@MainProjectId=0 or MainProjectId=@MainProjectId)
		 and ((select e.ename from employee e where e.username=Orders.username) like  '%'+@SText+'%' or isnull((Select CustomerName From Customer Where CustomerId=Orders.CustomerId),'...') like '%'+@SText+'%')
	)
	
	Select * From list Where Row between @StartIndex and @EndIndex order by fixturedate 
	end
	RETURN
